package com.dayu.helper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySQLHelper {

    /***
     * 数据库链接串（动态）
     */
    public static String DataBaseContentUrl = "jdbc:mysql://%s";
    /***
     * 驱动类
     */
    public static final String DriverClassName = "com.mysql.cj.jdbc.Driver";

    /***
     * 获取数据库库列表
     * @return
     * @throws SQLException
     */
    public static List<String> QueryDataBaseList() throws SQLException {
        DataBaseContentUrl = String.format("jdbc:mysql://%s", EntryHelper.DataBaseUrl);

        List<String> listDatabase = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery( "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')");
        while (resultSet.next()) {
            String schemaName = resultSet.getString("SCHEMA_NAME");
            listDatabase.add(schemaName);
        }
        return listDatabase;
    }

    /***
     * 获取库下所有表
     * 格式：表名 - 表描述
     * @return
     * @throws SQLException
     */
    public static List<String> QueryAllTable() throws SQLException{
        DataBaseContentUrl = String.format("jdbc:mysql://%s/%s", EntryHelper.DataBaseUrl, EntryHelper.DataBaseSele);

        List<String> listTable = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = '" + EntryHelper.DataBaseSele + "'");
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            String tableComment = resultSet.getString("TABLE_COMMENT");
            listTable.add(tableName + " - " + tableComment);
        }
        return listTable;
    }

    /**
     * 测试数据库连接
     * @return
     * @throws SQLException
     */
    public static boolean verifyConnection() throws SQLException {
        try {
            DataBaseContentUrl = String.format("jdbc:mysql://%s", EntryHelper.DataBaseUrl);
            Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
            if (connection != null) {
                connection.close();
                return true;
            }
        } catch (SQLException e) {
            throw e;
        }
        return false;
    }
}
